using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Entites;

namespace DAL
{
  public  class SanPhamDAL
    {
         private SqlConnection cn = null;
        public SanPhamDAL()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
            cn.Open();
        }
       public void Insert (SanPham pham)
        {
            SqlCommand cm = new SqlCommand("SanPham_insert", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@TenSP",pham.tensp);
           cm.Parameters.AddWithValue("@DanhMuc",pham.danhmuc);
           cm.Parameters.AddWithValue("@DoanhNghiep",pham.doanhnghiep);
           cm.Parameters.AddWithValue("@GiaGoc",pham.giagoc);
           cm.Parameters.AddWithValue("@GiaBan",pham.giaban);
           cm.Parameters.AddWithValue("@NgayBatDau",pham.ngaybatdau);
           cm.Parameters.AddWithValue("@NgayHetHan",pham.ngayhethan);
           cm.Parameters.AddWithValue("@SoLuongBan",pham.soluongban);
           cm.Parameters.AddWithValue("@SoLuongToiDa",pham.soluongtoida);
           cm.Parameters.AddWithValue("@MoTa",pham.mota);
           cm.Parameters.AddWithValue("@ChietKhau",pham.chietkhau);
           cm.Parameters.AddWithValue("@TinhTrang",pham.tinhtrang);
           cm.Parameters.AddWithValue("@Hinh",pham.hinh);

            cm.ExecuteNonQuery();
        }
        public void Update(SanPham pham)
        {
            SqlCommand cm = new SqlCommand("SanPham_update", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaSP", pham.masp);
            cm.Parameters.AddWithValue("@TenSP", pham.tensp);
            cm.Parameters.AddWithValue("@DanhMuc", pham.danhmuc);
            cm.Parameters.AddWithValue("@DoanhNghiep", pham.doanhnghiep);
            cm.Parameters.AddWithValue("@GiaGoc", pham.giagoc);
            cm.Parameters.AddWithValue("@GiaBan", pham.giaban);
            cm.Parameters.AddWithValue("@NgayBatDau", pham.ngaybatdau);
            cm.Parameters.AddWithValue("@NgayHetHan", pham.ngayhethan);
            cm.Parameters.AddWithValue("@SoLuongBan", pham.soluongban);
            cm.Parameters.AddWithValue("@SoLuongToiDa", pham.soluongtoida);
            cm.Parameters.AddWithValue("@MoTa", pham.mota);
            cm.Parameters.AddWithValue("@ChietKhau", pham.chietkhau);
            cm.Parameters.AddWithValue("@TinhTrang", pham.tinhtrang);
            cm.Parameters.AddWithValue("@Hinh", pham.hinh);
          
          
            cm.ExecuteNonQuery();
        }
        public void Delete(int iD)
        {
            SqlCommand cm = new SqlCommand("SanPham_delete", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaSP", iD);
            cm.ExecuteNonQuery();
        }

        public tbSanPhamCollection Get(string where, int pageIndex, int pageSize,
            string orderBy, string orderDirection, out int totalRecords)
        {
            SqlCommand cm = new SqlCommand("SanPham_get", cn);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", where);
            cm.Parameters.AddWithValue("@PageIndex", pageIndex);
            cm.Parameters.AddWithValue("@PageSize", pageSize);
            cm.Parameters.AddWithValue("@OrderBy", orderBy);
            cm.Parameters.AddWithValue("@OrderDirection", orderDirection);
            cm.Parameters.Add(new SqlParameter("@TotalRecords", DbType.Int32)).Direction = ParameterDirection.Output;

            SqlDataReader dr = cm.ExecuteReader();
            tbSanPhamCollection phamCollection = new tbSanPhamCollection();

            while (dr.Read())
            {
                SanPham sanpham = new SanPham();
                sanpham.masp = int.Parse(dr.GetValue(0).ToString());
                sanpham.tensp = dr.GetValue(1).ToString();
                sanpham.danhmuc = int.Parse(dr.GetValue(2).ToString());
                sanpham.doanhnghiep = int.Parse(dr.GetValue(3).ToString());
                sanpham.giagoc = int.Parse(dr.GetValue(4).ToString());
                sanpham.giaban = int.Parse(dr.GetValue(5).ToString());
                sanpham.ngaybatdau = DateTime.Parse(dr.GetValue(6).ToString());
                sanpham.ngayhethan = DateTime.Parse(dr.GetValue(7).ToString());
                sanpham.soluongban = float.Parse(dr.GetValue(8).ToString());
                sanpham.soluongtoida = float.Parse(dr.GetValue(9).ToString());
                sanpham.mota = dr.GetValue(10).ToString();
                sanpham.chietkhau = float.Parse(dr.GetValue(11).ToString());
                sanpham.tinhtrang = int.Parse(dr.GetValue(12).ToString());
                sanpham.hinh = dr.GetValue(13).ToString();

                phamCollection.Add(sanpham);


             
            }
            dr.Close();
            totalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return phamCollection;
        }
    }
    }

